﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using AyuBookmark.Common.Interfaces;
using AyuBookmark.Common.Models;
using Microsoft.Data.Sqlite;
using System.Data;

namespace AyuBookmark.Common.Repositories
{
    /// <summary>
    /// 通知仓储实现
    /// </summary>
    public class NotificationRepository : INotificationRepository
    {
        private readonly string _connectionString;
        private const string TableName = "Notifications";

        public NotificationRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<Notification> GetByIdAsync(int id)
        {
            //using var connection = await GetConnectionAsync();
            //return await connection.QueryFirstOrDefaultAsync<Notification>(
            //    $"SELECT * FROM {TableName} WHERE Id = @id",
            //    new { id });

            using var connection = await GetConnectionAsync();
            var notification = await connection.QueryFirstOrDefaultAsync<Notification>(
                $"SELECT * FROM {TableName} WHERE Id = @id",
                new { id });

            if (notification != null)
            {
                notification.User = await connection.QueryFirstOrDefaultAsync<User>(
                    "SELECT * FROM Users WHERE Id = @userId",
                    new { userId = notification.UserId });
            }

            return notification;

        }

        public async Task AddAsync(Notification notification)
        {
            using var connection = await GetConnectionAsync();
            await connection.ExecuteAsync(
                $@"INSERT INTO {TableName} 
                   (Title, Content, Type, Level, IsRead, UserId, CreatedTime)
                   VALUES (@Title, @Content, @Type, @Level, @IsRead, @UserId, @CreatedTime)",
                notification);
        }

        public async Task UpdateAsync(Notification notification)
        {
            using var connection = await GetConnectionAsync();
            await connection.ExecuteAsync(
                $@"UPDATE {TableName} SET 
                   Title = @Title, 
                   Content = @Content,
                   Type = @Type,
                   Level = @Level,
                   IsRead = @IsRead,
                   ReadTime = @ReadTime
                   WHERE Id = @Id",
                notification);
        }

        public async Task<IEnumerable<Notification>> FindAsync(Func<Notification, bool> predicate)
        {
            using var connection = await GetConnectionAsync();
            var notifications = await connection.QueryAsync<Notification>(
                $"SELECT * FROM {TableName}");
            return notifications.Where(predicate);
        }

        //public async Task<PaginatedResult<Notification>> GetPagedAsync(
        //    Func<Notification, bool> predicate,
        //    int pageIndex,
        //    int pageSize,
        //    Func<Notification, object> orderBy,
        //    bool isDescending = true)
        //{
        //    using var connection = await GetConnectionAsync();
        //    var notifications = (await connection.QueryAsync<Notification>(
        //        $"SELECT * FROM {TableName}"))
        //        .Where(predicate)
        //        .OrderBy(orderBy)
        //        .ToList();

        //    return new PaginatedResult<Notification>
        //    {
        //        Items = notifications.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        TotalCount = notifications.Count,
        //        PageIndex = pageIndex,
        //        PageSize = pageSize
        //    };
        //}

        // 文件路径：AyuBookmark.Common/Repositories/NotificationRepository.cs
        public async Task<PaginatedResult<Notification>> GetPagedAsync(
            Func<Notification, bool> predicate,
            int pageIndex,
            int pageSize,
            Func<Notification, object> orderBy,
            bool isDescending = true)
        {
            using var connection = await GetConnectionAsync();

            // 获取总数
            var totalCount = await connection.ExecuteScalarAsync<int>(
                $"SELECT COUNT(*) FROM {TableName} WHERE UserId = @userId",
                new { userId = predicate.Target.GetType().GetProperty("UserId")?.GetValue(predicate.Target) });

            // 获取分页数据
            var sql = $@"SELECT * FROM {TableName} 
                WHERE UserId = @userId
                ORDER BY {(isDescending ? "DESC" : "ASC")}
                LIMIT @pageSize OFFSET @offset";

            var items = await connection.QueryAsync<Notification>(sql, new
            {
                userId = predicate.Target.GetType().GetProperty("UserId")?.GetValue(predicate.Target),
                pageSize,
                offset = (pageIndex - 1) * pageSize
            });

            return new PaginatedResult<Notification>
            {
                Items = items,
                TotalCount = totalCount,
                PageIndex = pageIndex,
                PageSize = pageSize
            };
        }

        private async Task<IDbConnection> GetConnectionAsync()
        {
            var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();
            return connection;
        }
    }
}